package cn.iocoder.yudao.module.vos.dal.mysql.marketingscore;

import cn.iocoder.yudao.module.vos.controller.admin.marketingscore.vo.MarketingScorePageReqVO;

import java.time.*;
import java.time.format.DateTimeFormatter;
import java.util.*;

/** 必须 public */
public class MarketingScoreSqlProvider {

    // 推荐与你 e_cdr 一致：菲律宾时区
    private static final ZoneId ZONE = ZoneId.of("Asia/Manila");
    private static final DateTimeFormatter YMD = DateTimeFormatter.ofPattern("yyyyMMdd");

    /** 分表前缀：submeter_marketing_score -> submeter_marketing_score_yyyyMMdd */
    private static String baseTable() {
        return "submeter_marketing_score";
    }

    private static List<String> resolveTables(MarketingScorePageReqVO req) {
        List<String> tbs = new ArrayList<>();

        // ① 先看 listDate（LocalDate[]）
        LocalDate[] ld = req.getListDate();
        if (ld != null && ld.length > 0 && (ld[0] != null || (ld.length >= 2 && ld[1] != null))) {
            LocalDate start = (ld[0] != null) ? ld[0] : ld[1];
            LocalDate end   = (ld.length >= 2 && ld[1] != null) ? ld[1] : ld[0];
            if (end.isBefore(start)) { LocalDate tmp = start; start = end; end = tmp; }
            for (LocalDate d = start; !d.isAfter(end); d = d.plusDays(1)) {
                tbs.add(baseTable() + "_" + d.format(YMD));
            }
            return tbs;
        }

        // ② 再看 createTime（LocalDateTime[]）
        LocalDateTime[] ct = req.getCreateTime();
        boolean ctEmpty = (ct == null || ct.length == 0)
                || (ct.length == 1 && ct[0] == null)
                || (ct.length >= 2 && ct[0] == null && ct[1] == null);
        if (!ctEmpty) {
            LocalDateTime fromLdt = (ct.length >= 1 && ct[0] != null) ? ct[0] : ct[1];
            LocalDateTime toLdt   = (ct.length >= 2 && ct[1] != null) ? ct[1] : ct[0];
            if (toLdt.isBefore(fromLdt)) { LocalDateTime tmp = fromLdt; fromLdt = toLdt; toLdt = tmp; }
            LocalDate d1 = fromLdt.atZone(ZONE).toLocalDate();
            LocalDate d2 = toLdt.atZone(ZONE).toLocalDate();
            for (LocalDate d = d1; !d.isAfter(d2); d = d.plusDays(1)) {
                tbs.add(baseTable() + "_" + d.format(YMD));
            }
            return tbs;
        }

        // ③ 都没传，默认今天（马尼拉）
        LocalDate today = LocalDate.now(ZONE);
        tbs.add(baseTable() + "_" + today.format(YMD));
        return tbs;
    }


    /** 复用 WHERE 片段 */
    private static void appendWhere(StringBuilder sb) {
        sb.append(" WHERE 1=1 ");

        sb.append("<if test=\"req.score != null\"> AND score = #{req.score} </if>");
        sb.append("<if test=\"req.phoneMd5 != null and req.phoneMd5 != ''\"> AND phone_md5 = #{req.phoneMd5} </if>");
        sb.append("<if test=\"req.phoneTags != null and req.phoneTags != ''\"> AND phone_tags = #{req.phoneTags} </if>");
        sb.append("<if test=\"req.listLevel != null and req.listLevel != ''\"> AND list_level = #{req.listLevel} </if>");

        // —— create_time 时间范围（原逻辑保留）——
        sb.append("<if test=\"req.createTime != null "
                + "and req.createTime.length &gt;= 2 "
                + "and req.createTime[0] != null "
                + "and req.createTime[1] != null\">");
        sb.append(" AND create_time BETWEEN #{req.createTime[0]} AND #{req.createTime[1]} </if>");

        sb.append("<if test=\"req.createTime != null "
                + "and req.createTime.length &gt;= 1 "
                + "and req.createTime[0] != null "
                + "and !(req.createTime.length &gt;= 2 and req.createTime[1] != null)\">");
        sb.append(" AND create_time &gt;= #{req.createTime[0]} </if>");

        sb.append("<if test=\"req.createTime != null "
                + "and req.createTime.length &gt;= 2 "
                + "and req.createTime[1] != null "
                + "and (req.createTime[0] == null)\">");
        sb.append(" AND create_time &lt;= #{req.createTime[1]} </if>");

        // —— listDate 日期范围（根据你表结构二选一）——

        // A) 有 list_date 列（DATE）
        sb.append("<if test=\"req.listDate != null and req.listDate.length &gt;= 1 and req.listDate[0] != null\">");
        sb.append(" AND list_date &gt;= #{req.listDate[0]} </if>");
        sb.append("<if test=\"req.listDate != null and req.listDate.length &gt;= 2 and req.listDate[1] != null\">");
        sb.append(" AND list_date &lt;= #{req.listDate[1]} </if>");

        // B) 如果没有 list_date 列，改成：
        // sb.append("<if test=\"req.listDate != null and req.listDate.length &gt;= 1 and req.listDate[0] != null\">");
        // sb.append(" AND DATE(create_time) &gt;= #{req.listDate[0]} </if>");
        // sb.append("<if test=\"req.listDate != null and req.listDate.length &gt;= 2 and req.listDate[1] != null\">");
        // sb.append(" AND DATE(create_time) &lt;= #{req.listDate[1]} </if>");
    }


    /** 分页查询（UNION ALL 后再 ORDER/LIMIT） */
    public static String buildSelectPageScript(Map<String, Object> params) {
        MarketingScorePageReqVO req = (MarketingScorePageReqVO) params.get("req");
        List<String> tbs = resolveTables(req);
        if (tbs.isEmpty()) {
            LocalDate today = LocalDate.now(ZONE);
            tbs.add(baseTable() + "_" + today.format(YMD));
        }

        StringBuilder sb = new StringBuilder(4096);
        sb.append("<script>");
        sb.append("SELECT * FROM (");
        for (int i = 0; i < tbs.size(); i++) {
            if (i > 0) sb.append(" UNION ALL ");
            sb.append("SELECT * FROM ").append(tbs.get(i));
            appendWhere(sb);
        }
        sb.append(") AS u ");
        sb.append(" ORDER BY u.create_time DESC, u.phone_e164 DESC ");

        // ⭐ 关键修改：limit = -1（不分页导出）时，不拼 LIMIT/OFFSET
        sb.append("<if test=\"limit != null and limit != -1\">");
        sb.append(" LIMIT #{limit} OFFSET #{offset}");
        sb.append("</if>");

        sb.append("</script>");
        return sb.toString();
    }

    /** 统计总数（各分表分别 count 再求和） */
    public static String buildCountScript(Map<String, Object> params) {
        MarketingScorePageReqVO req = (MarketingScorePageReqVO) params.get("req");
        List<String> tbs = resolveTables(req);
        if (tbs.isEmpty()) {
            LocalDate today = LocalDate.now(ZONE);
            tbs.add(baseTable() + "_" + today.format(YMD));
        }

        StringBuilder sb = new StringBuilder(2048);
        sb.append("<script>");
        sb.append("SELECT SUM(cnt) FROM (");
        for (int i = 0; i < tbs.size(); i++) {
            if (i > 0) sb.append(" UNION ALL ");
            sb.append("SELECT COUNT(1) AS cnt FROM ").append(tbs.get(i));
            appendWhere(sb);
        }
        sb.append(") AS c");
        sb.append("</script>");
        return sb.toString();
    }
}
